package Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;


import Conexao.ConnectionManager;
import Modelo.Aluno;


public class AlunoDaoImp implements AlunoDao {
	
	private final String INSERT = "insert into aluno (nome, matricula, email, cpf, curso) values (?,?,?,?,?)";
	private final String UPDATE = "update aluno set nome = ?, matricula = ?, email = ?, cpf = ?, curso = ? where idAluno = ?";
	private final String DELETE = "delete from aluno where idAluno = ?";
	private final String ALL = "select * from aluno";
	  private final String ID = "select * from Aluno where idAluno = ?";
	  private final String CPF = "select * from Aluno where cpf = ?";

	private static Connection connection;
	
	public AlunoDaoImp() {
		openConnection();
		
	}
	
	public Aluno salvar(Aluno e) {
		try {
			PreparedStatement ps = null;

			ps = ConnectionManager.getInstance().getConnection()
					.prepareStatement(INSERT);

			ps.setObject(1, e.getNome());
			ps.setObject(2, e.getMatricula());
			ps.setObject(3, e.getEmail());
			ps.setObject(4, e.getCpf());
			ps.setObject(5, e.getCurso());
			
			
			ps.executeUpdate();

		} catch (SQLException ex) {
			Logger.getLogger(AlunoDaoImp.class.getName()).log(Level.SEVERE,
					null, ex);
		} finally {
			this.closeConnection();
		}
		return e;
	}
	
	public void alterar(Aluno e) {
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(UPDATE);

			ps.setObject(1, e.getNome());
			ps.setObject(2, e.getMatricula());
			ps.setObject(3, e.getEmail());
			ps.setObject(4, e.getCpf());
			ps.setObject(5, e.getCurso());
			ps.setObject(6, e.getIdAluno());

			ps.executeUpdate();

		} catch (SQLException ex) {
			Logger.getLogger(AlunoDaoImp.class.getName()).log(Level.SEVERE,
					null, ex);
		} finally {
			this.closeConnection();
		}
	}
	
	public void deletar(Aluno e) {
		try {
			PreparedStatement ps = null;
			ps = connection.prepareStatement(DELETE);

			ps.setObject(1, e.getIdAluno());

			ps.executeUpdate();

		} catch (SQLException ex) {
			Logger.getLogger(AlunoDaoImp.class.getName()).log(Level.SEVERE,
					null, ex);
		} finally {
			this.closeConnection();
		}
	}

	public List<Aluno> findAll() {
		List<Aluno> alunos = new ArrayList<Aluno>();

		try {
			PreparedStatement ps = null;
			ResultSet rs = null;

			ps = connection.prepareStatement(ALL);

			rs = ps.executeQuery();

			while (rs.next()) {
				Aluno aluno = new Aluno();
				aluno.setIdAluno(rs.getInt("idAluno"));
				aluno.setNome(rs.getString("nome"));
				aluno.setMatricula(rs.getString("matricula"));
				aluno.setEmail(rs.getString("email"));
				aluno.setCpf(rs.getString("cpf"));
				aluno.setCurso(rs.getString("curso"));
				
				alunos.add(aluno);
			}

		} catch (SQLException ex) {
			Logger.getLogger(AlunoDaoImp.class.getName()).log(Level.SEVERE,
					null, ex);
		} finally {
			this.closeConnection();
		}
		return alunos;
	}
	
	public boolean isConnectionClose() {
		try {
			return connection.isClosed();
		} catch (SQLException ex) {
			return false;
		}
	}

	public void openConnection() {
		if (connection == null) {
			connection = ConnectionManager.getInstance().getConnection();
		}

		if (isConnectionClose()) {
			connection = ConnectionManager.getInstance().getConnection();
		}
	}


	public void closeConnection() {
		if (connection != null && !this.isConnectionClose()) {
			ConnectionManager.getInstance().closeConnection(connection);
		}

	}

	@Override
	public Aluno BuscarPorId(int idAluno) {
		List<Aluno> alunos = new ArrayList<Aluno>();

		try {
		PreparedStatement ps = null;
		ResultSet rs = null;

		ps = connection.prepareStatement(ID, idAluno);
		ps.setObject(1, idAluno );

		rs = ps.executeQuery();

		while (rs.next()) {
			Aluno aluno = new Aluno();
			aluno.setIdAluno(rs.getInt("idAluno"));
			aluno.setNome(rs.getString("nome"));
			aluno.setMatricula(rs.getString("matricula"));
			aluno.setEmail(rs.getString("email"));
			aluno.setCpf(rs.getString("cpf"));
			aluno.setCurso(rs.getString("curso"));
			
			alunos.add(aluno);
		}

		} catch (SQLException ex) {
		Logger.getLogger(AlunoDaoImp.class.getName()).log(Level.SEVERE,
		null, ex);
		} finally {
		this.closeConnection();
		}
		if (alunos.isEmpty()) {
		return null;
		} else {
		return alunos.get(0);
		}
	}

	@Override
	public Aluno buscarPorCpf(String cpf) {
		List<Aluno> alunos = new ArrayList<Aluno>();

		try {
		PreparedStatement ps = null;
		ResultSet rs = null;

		ps = connection.prepareStatement(CPF);
		ps.setObject(1, cpf );

		rs = ps.executeQuery();

		while (rs.next()) {
			Aluno aluno = new Aluno();
			aluno.setIdAluno(rs.getInt("idAluno"));
			aluno.setNome(rs.getString("nome"));
			aluno.setMatricula(rs.getString("matricula"));
			aluno.setEmail(rs.getString("email"));
			aluno.setCpf(rs.getString("cpf"));
			aluno.setCurso(rs.getString("curso"));
			
			alunos.add(aluno);
		}

		} catch (SQLException ex) {
		Logger.getLogger(AlunoDaoImp.class.getName()).log(Level.SEVERE,
		null, ex);
		} finally {
		this.closeConnection();
		}
		if (alunos.isEmpty()) {
		return null;
		} else {
		return alunos.get(0);
		}
	
	}
}


